from flask import request,jsonify,Blueprint
from db import db1
from comm import get_data
bp_user=Blueprint('user',__name__)
@bp_user.route('/users',methods=['GET','POST','PUT','DELETE'])
def users():
    if request.method=='GET':
        id=request.args.get('id')
        name=request.args.get('name')
        zhanghao=request.args.get('zhanghao')
        phone=request.args.get('phone')
        print([id,name,zhanghao,phone])
        if id:
            sql="select * from usergl where id =%d"%(int(id))
            res=db1.find_one(sql)
            return jsonify({'list':res})
        else:
            if name:
                sql=f"select * from usergl where name like '%{name}%'"
                res=db1.find(sql)
                return jsonify({'list':res})
            elif zhanghao:      
                sql=f"select * from usergl where zhanghao='{zhanghao}'"
                res=db1.find(sql)
                return jsonify({'list':res})
            elif phone:
                sql=f"select * from usergl where phone={phone}"
                res=db1.find(sql)
                return jsonify({'list':res})
            else:
                sql="select * from usergl"
                res=db1.find(sql)
                return jsonify({'list':res})
                  
    elif request.method=='POST':
        data=get_data()
        print(data)
        sql="insert into usergl(zhanghao,name,gender,phone,youx,num,ztai) values('%s','%s','%s','%s','%s','%s','%s')"%(data['zhanghao'],data['name'],data['gender'],data['phone'],data['youx'],data['num'],data['ztai'])
        res=db1.insert(sql)
        return jsonify({'mes':"添加成功"})
    
    elif request.method=='DELETE':
        id=request.args.get('id')
        sql="delete from usergl where id=%d"%int(id)
        res=db1.insert(sql) 
        return jsonify({'mes':"删除成功"})
    else:
        data=get_data()
        sql="update usergl set name='%s',zhanghao='%s',gender='%s',phone='%s',youx='%s',num='%s',ztai='%s' where id=%d"%(data['name'],data['zhanghao'],data['gender'],data['phone'],data['youx'],data['num'],data['ztai'],int(data['id']))
        res=db1.insert(sql) 
        return jsonify({'mes':"修改成功"})





@bp_user.route('/worklist')
def worklist():
    sql="select * from workflow"
    res=db1.find(sql)
    return jsonify({"code":200,'list':res})


from redis01 import redism
import json
#获取数据源接口
@bp_user.route('/getresourcedata')
def getresourcedata():
    #从redis取，如果有直接返回
    values = redism.get_str('dataresouce')
    if values:
        list = eval(values)
    else:
        #如果没有查询
        sql = "select * from data_source"
        res = db1.find_all(sql)
        list = []
        if res:
            for i in res:
                dict={'id':i['id'],'name':i['name']}
                sql1 = "select id,name,type from %s"%(i['tablename'])
                subres = db1.find_all(sql1)
                dict['mes'] = subres
                list.append(dict)
        #存入redis
        redism.set_str('dataresouce',json.dumps(list))
    return jsonify({"code":200,'list':list})





from excels import excel
@bp_user.route('/udao')
def dao():
    sql="select name,zhanghao,gender,phone,youx,num,ztai from gangwgl"
    lst=['姓名','账号','性别','手机号','邮箱','工号','状态']
    filename='filex.xls'
    excel(sql,lst,filename)
    return jsonify({'url':'http://localhost:5000/static/download/'+filename})
from excels import excelr



@bp_user.route('/udaor',methods=['POST'])
def daor():
    file=request.files.get('file')
    file.save('D:\\p9\pro\\workflow\\static\\upload\\'+file.filename)
    filename1='D:\\p9\pro\\workflow\\static\\upload\\'+str(file.filename)
    sql1="insert into gangwgl(name,zhanghao,gender,number,youx,num,ztai) values"
    num1=4
    excelr(sql1,filename1,num1)
    return jsonify({'mes':'成功'})
